package com.scuec.groupfive.mapper;

import com.scuec.groupfive.dto.PastExamAvgScore;
import com.scuec.groupfive.entity.*;
import org.apache.ibatis.annotations.*;
import java.util.List;
import java.util.Map;

@Mapper
public interface UserMockMapper {

    // ===================== 原有方法（全部保留） =====================
    // 1. 完全随机策略
    @Select({
            "<script>",
            "SELECT q.* FROM questions q",
            "WHERE q.subject_id IN",
            "<foreach collection='subjectIds' item='id' open='(' separator=',' close=')'>",
            "   #{id}",
            "</foreach>",
            "AND q.question_type = #{questionType}",
            "AND q.type_id = #{typeId}",
            "AND q.status = 1",
            "ORDER BY RAND()",
            "LIMIT #{count}",
            "</script>"
    })
    List<QuestionInfo> findRandomQuestionsBySubjectAndType(
            @Param("subjectIds") List<Integer> subjectIds,
            @Param("questionType") String questionType,
            @Param("count") int count,
            @Param("typeId") int typeId
    );

    // 2. 薄弱知识点策略 - 错题本题目
    @Select({
            "SELECT q.* FROM questions q",
            "JOIN wrong_questions wq ON q.question_id = wq.question_id",
            "WHERE wq.user_id = #{userId}",
            "AND q.question_type = #{questionType}",
            "AND q.status = 1",
            "ORDER BY RAND()",
            "LIMIT #{count}"
    })
    List<QuestionInfo> getWrongQuestionsByUserAndType(
            @Param("userId") Long userId,
            @Param("questionType") String questionType,
            @Param("count") int count
    );

    // 3. 薄弱知识点策略 - 知识点关联题目
    @Select({
            "<script>",
            "SELECT q.* FROM questions q",
            "JOIN question_knowledge qk ON q.question_id = qk.question_id",
            "WHERE qk.point_id IN",
            "<foreach collection='knowledgePointIds' item='pointId' open='(' separator=',' close=')'>",
            "   #{pointId}",
            "</foreach>",
            "AND q.question_type = #{questionType}",
            "AND q.status = 1",
            "ORDER BY RAND()",
            "LIMIT #{count}",
            "</script>"
    })
    List<QuestionInfo> getQuestionsByKnowledgePoints(
            @Param("knowledgePointIds") List<Integer> knowledgePointIds,
            @Param("questionType") String questionType,
            @Param("count") int count
    );

    // 4. 定向知识点策略
    @Select({
            "<script>",
            "SELECT q.* FROM questions q",
            "JOIN question_knowledge qk ON q.question_id = qk.question_id",
            "WHERE qk.point_id IN",
            "<foreach collection='knowledgePointIds' item='pointId' open='(' separator=',' close=')'>",
            "   #{pointId}",
            "</foreach>",
            "AND q.question_type = #{questionType}",
            "AND q.type_id = #{typeId}",  // 关键：按考试类型过滤
            "AND q.status = 1",
            "ORDER BY RAND()",  // 随机排序获取
            "LIMIT #{count}",
            "</script>"
    })
    List<QuestionInfo> getCustomQuestionsByKnowledgePoints(
            @Param("knowledgePointIds") List<Integer> knowledgePointIds,
            @Param("questionType") String questionType,
            @Param("count") int count,
            @Param("typeId") int typeId);

    // 5. 根据科目ID获取知识点
    @Select({
            "<script>",
            "SELECT * FROM knowledge_points WHERE subject_id IN",
            "<foreach collection='subjectIds' item='id' separator=',' open='(' close=')'>",
            "   #{id}",
            "</foreach>",
            "</script>"
    })
    List<KnowledgePoint> getKnowledgePointsBySubjectIds(@Param("subjectIds") List<Integer> subjectIds);

    // 6. 获取用户薄弱知识点ID列表
    @Select({
            "SELECT DISTINCT qk.point_id FROM question_knowledge qk",
            "JOIN wrong_questions wq ON qk.question_id = wq.question_id",
            "WHERE wq.user_id = #{userId}"
    })
    List<Integer> getWeakKnowledgePointIdsByUser(@Param("userId") Long userId);

    // 7. 根据知识点ID获取所属科目ID
    @Select("SELECT subject_id FROM knowledge_points WHERE point_id = #{pointId}")
    Integer getSubjectIdByPointId(@Param("pointId") Integer pointId);

    // 8. 统计题目数量
    @Select({
            "<script>",
            "SELECT COUNT(*) FROM questions WHERE status = 1",
            "AND subject_id IN",
            "<foreach collection='subjectIds' item='id' open='(' separator=',' close=')'>",
            "   #{id}",
            "</foreach>",
            "AND question_type = #{questionType}",
            "</script>"
    })
    int countQuestionsBySubjectAndType(
            @Param("subjectIds") List<Integer> subjectIds,
            @Param("questionType") String questionType
    );

    // 9. 插入模拟考试记录
    @Insert("INSERT INTO mock_exams(user_id, exam_type, exam_strategy, total_questions, total_time, score) " +
            "VALUES(#{userId}, #{examType}, #{examStrategy}, #{totalQuestions}, #{totalTime}, #{score})")
    @Options(useGeneratedKeys = true, keyProperty = "examId")
    int insertMockExam(MockExam mockExam);

    // 10. 插入考试题目记录
    @Insert("INSERT INTO exam_questions(exam_id, question_id, user_answer, is_correct, sequence, question_type) " +
            "VALUES(#{examId}, #{questionId}, #{userAnswer}, #{isCorrect}, #{sequence}, #{questionType})")
    int insertExamQuestion(ExamQuestion examQuestion);

    // 11. 插入答题记录
    @Insert("INSERT INTO answer_records(user_id, question_id, question_type, answer, is_correct, score, time_spent) " +
            "VALUES(#{userId}, #{questionId}, #{questionType}, #{answer}, #{isCorrect}, #{score}, #{timeSpent})")
    int insertAnswerRecord(AnswerRecord answerRecord);

    // 12. 插入或更新错题记录
    @Insert("INSERT INTO wrong_questions(user_id, question_id, wrong_count, subject_id) " +
            "VALUES(#{userId}, #{questionId}, #{wrongCount}, #{subjectId}) " +
            "ON DUPLICATE KEY UPDATE wrong_count = wrong_count + 1")
    int insertOrUpdateWrongQuestion(WrongQuestion wrongQuestion);

    // 13. 更新模拟考试分数
    @Update("UPDATE mock_exams SET score = #{score} WHERE exam_id = #{examId}")
    int updateMockExamScore(@Param("examId") Long examId, @Param("score") Double score);

    // 14. 根据ID查询题目
    @Select("SELECT * FROM questions WHERE question_id = #{questionId}")
    QuestionInfo selectQuestionById(@Param("questionId") Long questionId);

    // 15. 查询题目所属科目ID
    @Select("SELECT subject_id FROM questions WHERE question_id = #{questionId}")
    Integer selectSubjectIdByQuestionId(@Param("questionId") Long questionId);

    // 16. 查询选择题详情
    @Select("SELECT * FROM choice_questions WHERE question_id = #{questionId}")
    ChoiceQuestion selectChoiceByQuestionId(@Param("questionId") Long questionId);

    // 17. 查询填空题详情
    @Select("SELECT * FROM fill_blank_questions WHERE question_id = #{questionId}")
    FillBlankQuestion selectFillBlankByQuestionId(@Param("questionId") Long questionId);

    // 18. 查询主观题详情
    @Select("SELECT * FROM subjective_questions WHERE question_id = #{questionId}")
    SubjectiveQuestion selectSubjectiveByQuestionId(@Param("questionId") Long questionId);

    // 19. 查询模拟考试记录
    @Select("SELECT * FROM mock_exams WHERE exam_id = #{examId}")
    MockExam selectMockExamById(@Param("examId") Long examId);

    // 20. 查询考试中的题目ID列表
    @Select("SELECT question_id FROM exam_questions WHERE exam_id = #{examId} ORDER BY sequence")
    List<Long> selectQuestionIdsByExamId(@Param("examId") Long examId);

    // 21. 批量查询题目（保留原有方法）
    @Select({
            "<script>",
            "SELECT * FROM questions WHERE question_id IN",
            "<foreach collection='questionIds' item='id' open='(' separator=',' close=')'>",
            "   #{id}",
            "</foreach>",
            "ORDER BY FIELD(question_id, ",
            "<foreach collection='questionIds' item='id' separator=','>",
            "   #{id}",
            "</foreach>",
            ")",
            "</script>"
    })
    List<QuestionInfo> selectQuestionsByIds(@Param("questionIds") List<Long> questionIds);

    // 22. 获取题目题型
    @Select("SELECT question_type FROM questions WHERE question_id = #{questionId}")
    String getQuestionTypeById(@Param("questionId") Long questionId);

    // 23. 获取选择题答案
    @Select("SELECT answer FROM choice_questions WHERE question_id = #{questionId}")
    String getChoiceQuestionAnswer(@Param("questionId") Long questionId);

    @Select("SELECT answer_template FROM fill_blank_questions WHERE question_id = #{questionId}")
    String getFillBlankQuestionAnswer(@Param("questionId") Long questionId);

    // 25. 获取主观题答案
    @Select("SELECT reference_answer FROM subjective_questions WHERE question_id = #{questionId}")
    String getSubjectiveQuestionAnswer(@Param("questionId") Long questionId);

    // 在 UserMockMapper.java 中添加/修改这个方法
    default String getCorrectAnswerByType(Long questionId, String questionType) {
        switch (questionType) {
            case "single_choice":
                ChoiceQuestion choice = selectChoiceByQuestionId(questionId);
                return choice != null ? choice.getAnswer() : null;
            case "fill_in_the_blank":
                FillBlankQuestion fillBlank = selectFillBlankByQuestionId(questionId);
                return fillBlank != null ? fillBlank.getAnswerTemplate() : null;
            case "subjective":
                SubjectiveQuestion subjective = selectSubjectiveByQuestionId(questionId);
                return subjective != null ? subjective.getReferenceAnswer() : null;
            default:
                return null;
        }
    }

    // 26. 更新模拟考试记录
    // 更新模拟考试记录
    @Update({
            "UPDATE mock_exams ",
            "SET score = #{mockExam.score}, ",
            "correct_questions = #{mockExam.correctQuestions}, ",
            "exam_date = #{mockExam.examDate}, ",
            "updated_at = CURRENT_TIMESTAMP ",
            "WHERE exam_id = #{mockExam.examId}"
    })
    int updateMockExam(@Param("mockExam") MockExam mockExam);
    // 27. 根据题目ID查询科目ID
    @Select("SELECT subject_id FROM questions WHERE question_id = #{questionId}")
    Integer getSubjectIdFromQuestions(@Param("questionId") Long questionId);

    // 28. 查询考试题目详情
    @Select("SELECT * FROM exam_questions WHERE exam_id = #{examId}")
    List<ExamQuestion> selectExamQuestionsByExamId(@Param("examId") Long examId);

    // 29. 查询单个考试题目
    @Select("SELECT * FROM exam_questions WHERE exam_id = #{examId} AND question_id = #{questionId}")
    ExamQuestion getExamQuestionByExamAndQuestion(@Param("examId") Long examId, @Param("questionId") Long questionId);

    // ===================== 新增：高频易错题策略核心方法 =====================
    // 30. 获取高频易错题（按所有用户错误次数总和降序）
    @Select({
            "<script>",
            "SELECT q.*, SUM(wq.wrong_count) as total_errors",
            "FROM questions q",
            "JOIN wrong_questions wq ON q.question_id = wq.question_id",
            "WHERE q.status = 1",
            "<if test='questionType != null'>",
            "  AND q.question_type = #{questionType}",
            "</if>",
            "<if test='typeId != null'>",
            "  AND q.type_id = #{typeId}",
            "</if>",
            "GROUP BY q.question_id",
            "ORDER BY total_errors DESC",
            "LIMIT #{count}",
            "</script>"
    })
    List<QuestionInfo> getHighFrequencyQuestionsByType(
            @Param("questionType") String questionType,
            @Param("count") int count,
            @Param("typeId") int typeId
    );

    // 31. 根据考试类型获取type_id（数一/数二/数三）
    // 确保这个方法能正确处理数三的查询
    @Select("SELECT type_id FROM exam_types WHERE exam_type = #{examType}")
    Integer findTypeIdByExamType(@Param("examType") String examType);

    // 32. 根据type_id获取科目ID列表
    @Select("SELECT subject_id FROM subjects WHERE subject_code LIKE CONCAT(#{typeId}, '%')")
    List<Integer> findSubjectIdsByExamTypeId(@Param("typeId") int typeId);

    // 按用户、题型和考试类型获取错题
    @Select({
            "SELECT q.* FROM questions q",
            "JOIN wrong_questions wq ON q.question_id = wq.question_id",
            "WHERE wq.user_id = #{userId}",
            "AND q.question_type = #{questionType}",
            "AND q.type_id = #{typeId}",  // 新增考试类型过滤
            "AND q.status = 1",
            "ORDER BY wq.wrong_count DESC",  // 按错误次数排序
            "LIMIT #{count}"
    })
    List<QuestionInfo> getWrongQuestionsByUserAndTypeAndExamType(
            @Param("userId") Long userId,
            @Param("questionType") String questionType,
            @Param("count") int count,
            @Param("typeId") int typeId);

    // 按知识点和考试类型获取题目
    @Select({
            "<script>",
            "SELECT q.* FROM questions q",
            "JOIN question_knowledge qk ON q.question_id = qk.question_id",
            "WHERE qk.point_id IN",
            "<foreach collection='knowledgePointIds' item='pointId' open='(' separator=',' close=')'>",
            "   #{pointId}",
            "</foreach>",
            "AND q.question_type = #{questionType}",
            "AND q.type_id = #{typeId}",  // 新增考试类型过滤
            "AND q.status = 1",
            "ORDER BY RAND()",
            "LIMIT #{count}",
            "</script>"
    })
    List<QuestionInfo> getQuestionsByKnowledgePointsAndExamType(
            @Param("knowledgePointIds") List<Integer> knowledgePointIds,
            @Param("questionType") String questionType,
            @Param("count") int count,
            @Param("typeId") int typeId);
    //------------------------------------------------------------------------------
    //----------------------历年套卷部分在下面--------------------------------------
    //---------------------------------------------------------------------------
    // 获取历年套卷列表
    // 获取历年套卷列表
    @Select({
            "SELECT record_id, exam_type, exam_year, total_questions, correct_questions, score, ",
            "COUNT(*) OVER() as total_count, AVG(score) OVER() as avg_score ",
            "FROM past_exam_records ",
            "WHERE exam_type = #{examType} AND exam_year = #{year} ",
            "ORDER BY exam_year DESC"
    })
    List<PastExamRecord> getPastExamRecordsByTypeAndYear(
            @Param("examType") String examType,
            @Param("year") int year
    );

    // 统计历年套卷数据
    @Select({
            "<script>" +
            "SELECT exam_year, exam_type, ",
            "COUNT(DISTINCT user_id) as exam_count, ",
            "AVG(score) as avg_score ",
            "FROM past_exam_records ",
            "WHERE 1=1 ",
            "<if test='year != null'>",
            "  AND exam_year = #{year}",
            "</if>",
            "GROUP BY exam_year, exam_type ",
            "ORDER BY exam_year DESC, exam_type",
            "</script>"
    })
    List<PastExamStat> getPastExamStats(@Param("year") Integer year);

    // 获取真题试卷题目（按原始顺序）
    @Select({
            "SELECT q.* FROM questions q",
            "WHERE q.year = #{year} AND q.type_id = (SELECT type_id FROM exam_types WHERE exam_type = #{examType})",
            "ORDER BY q.original_sequence"
    })
    List<QuestionInfo> getPastExamQuestions(
            @Param("examType") String examType,
            @Param("year") int year
    );
    // 统计用户考试次数
    // 修改统计用户考试次数的方法
    // UserMockMapper.java
    @Select({
            "SELECT exam_year AS examYear, exam_type AS examType, ",
            "COUNT(DISTINCT record_id) AS examCount ",
            "FROM past_exam_records ",
            "WHERE user_id = #{userId} ",
            "GROUP BY exam_year, exam_type"
    })
    List<PastExamStat> countMyPastExams(@Param("userId") Long userId);
    // UserMockMapper.java中添加
    @Select({
            "SELECT COUNT(*) FROM questions q",
            "JOIN exam_types et ON q.type_id = et.type_id",
            "WHERE et.exam_type = #{examType} AND q.year = #{year}"
    })
    int countPastExamQuestions(@Param("examType") String examType, @Param("year") int year);

    // UserMockMapper.java 中添加

    // 插入历年真题考试记录
    @Insert("INSERT INTO past_exam_records(user_id, username, exam_type, exam_year, exam_date, " +
            "total_questions, correct_questions, score, total_time) " +
            "VALUES(#{userId}, #{username}, #{examType}, #{examYear}, #{examDate}, " +
            "#{totalQuestions}, #{correctQuestions}, #{score}, #{totalTime})")
    @Options(useGeneratedKeys = true, keyProperty = "recordId")
    int insertPastExamRecord(PastExamRecord pastExamRecord);

    // 插入历年真题题目记录
    @Insert("INSERT INTO past_exam_questions(record_id, question_id, user_answer, is_correct, sequence, question_type) " +
            "VALUES(#{recordId}, #{questionId}, #{userAnswer}, #{isCorrect}, #{sequence}, #{questionType})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insertPastExamQuestion(PastExamQuestion pastExamQuestion);

    // 根据 record_id 查询题目列表
    @Select("SELECT * FROM past_exam_questions WHERE record_id = #{recordId} ORDER BY sequence")
    List<PastExamQuestion> selectPastExamQuestionsByRecordId(Long recordId);

    @Select("SELECT * FROM past_exam_records WHERE record_id = #{recordId}")
    PastExamRecord selectPastExamRecordById(Long recordId);

    // 更新历年考试记录
    @Update("UPDATE past_exam_records SET correct_questions = #{correctQuestions}, score = #{score} WHERE record_id = #{recordId}")
    int updatePastExamRecord(PastExamRecord record);

    // 获取历年套卷平均分
    @Select({
            "SELECT exam_year AS examYear, exam_type AS examType, ",
            "AVG(score) AS avgScore ",
            "FROM past_exam_records ",
            "GROUP BY exam_year, exam_type"
    })
    List<PastExamAvgScore> getPastExamAvgScores();
}